using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;

namespace HAZ.WinDBConnection.Forms
{
    public partial class ConPreco : UserControl
    {
        private ConnectionFactory.DBConnection dbConnection = new HAZ.ConnectionFactory.DBConnection();

        public ConPreco()
        {
            InitializeComponent();
        }

        private void textBox1_KeyUp(object sender, KeyEventArgs e)
        {
            #region Consulta Preço
            if (e.KeyCode == Keys.Enter)
            {
                try
                {
                    lblPreco.Text = String.Format("{0:C}", 0); //"R$ 0,00";
                    lblObs.Text   = "";
                    lblDesc.Text  = "";
                    imgProd.Image = null;

                    string        strRefBasica = "";
                    StringBuilder sQuery       = new StringBuilder();

                    sQuery.AppendLine("DECLARE @@ref  AS VARCHAR(20)");
                    sQuery.AppendLine("DECLARE @@loja AS VARCHAR(3)");
                    sQuery.AppendLine("DECLARE @@rede AS VARCHAR(3)");
                    sQuery.AppendLine("DECLARE @@hoje AS DATETIME");
                    sQuery.AppendLine("SET @@ref  = '#ref#' ");
                    sQuery.AppendLine("SET @@loja = '#loja#'");
                    sQuery.AppendLine("SET @@rede = '#rede#'");
                    sQuery.AppendLine("SET @@hoje = GetDate()");

                    sQuery.AppendLine("SELECT TOP #top# prd.Referencia, prd.RefBasica, prd.Descricao");
                    sQuery.AppendLine("     , prc.PrecoAtacado PrecoAtacado");
                    sQuery.AppendLine("     , IsNull((SELECT TOP 1 ppd.VendaPromocao");
                    sQuery.AppendLine("                 FROM PromocaoProduto ppd WITH(NOLOCK)");
                    sQuery.AppendLine("                INNER JOIN Promocao pro WITH(NOLOCK)");
                    sQuery.AppendLine("                   ON pro.ID_Promocao = ppd.ID_Promocao");
                    sQuery.AppendLine("                  AND pro.TipoPreco   = 1");
                    sQuery.AppendLine("                  AND @@hoje BETWEEN pro.DataInicial");
                    sQuery.AppendLine("                                 AND pro.DataFinal");
                    sQuery.AppendLine("                WHERE ppd.CodLoja    = @@loja");
                    sQuery.AppendLine("                  AND ppd.Referencia = prd.Referencia");
                    sQuery.AppendLine("                  AND @@hoje BETWEEN ppd.DataInicial");
                    sQuery.AppendLine("                                 AND ppd.DataFinal");
                    sQuery.AppendLine("                ORDER BY ppd.DataInicial DESC),0) PromocaoAtacado");
                    sQuery.AppendLine("     , prc.PrecoVarejo  PrecoVarejo");
                    sQuery.AppendLine("     , IsNull((SELECT TOP 1 ppd.VendaPromocao");
                    sQuery.AppendLine("                 FROM PromocaoProduto ppd WITH(NOLOCK)");
                    sQuery.AppendLine("                INNER JOIN Promocao pro WITH(NOLOCK)");
                    sQuery.AppendLine("                   ON pro.ID_Promocao = ppd.ID_Promocao");
                    sQuery.AppendLine("                  AND pro.TipoPreco   = 2");
                    sQuery.AppendLine("                  AND @@hoje BETWEEN pro.DataInicial");
                    sQuery.AppendLine("                                 AND pro.DataFinal");
                    sQuery.AppendLine("                WHERE ppd.CodLoja    = @@loja");
                    sQuery.AppendLine("                  AND ppd.Referencia = prd.Referencia");
                    sQuery.AppendLine("                  AND @@hoje BETWEEN ppd.DataInicial");
                    sQuery.AppendLine("                                 AND ppd.DataFinal");
                    sQuery.AppendLine("                ORDER BY ppd.DataInicial DESC),0) PromocaoVarejo");
                    sQuery.AppendLine("  FROM Produto prd WITH(NOLOCK)");
                    sQuery.AppendLine(" INNER JOIN GalPrecos prc WITH(NOLOCK)");
                    sQuery.AppendLine("    ON prc.Referencia   = prd.Referencia");
                    sQuery.AppendLine("   AND(prc.Referencia   = @@ref");
                    sQuery.AppendLine("    OR Substring(prd.Referencia,1,6) = @@ref");
                    sQuery.AppendLine("    OR Substring(prd.Referencia,1,8) = @@ref)");
                    sQuery.AppendLine("   AND prc.CodLoja        = @@loja");
                    sQuery.AppendLine(" WHERE(prd.RefBasica      = @@ref");
                    sQuery.AppendLine("    OR prd.Referencia     = @@ref)");
                    sQuery.AppendLine(" ORDER BY " + (primeiro.Checked ? "prd.PrecoVarejo DESC" : "prd.Referencia"));
                    
                    ConnectionFactory.IDBConnector dbConn = ConnectionFactory.DBFactory.GetConnection(dbConnection.SGBD);

                    string strQuery = sQuery.ToString();
                    strQuery = strQuery.Replace("#top#" , (primeiro.Checked ? "1" : nudMaxReturn.Value.ToString()));
                    strQuery = strQuery.Replace("#ref#" , txtReferencia.Text.Trim()                               );
                    strQuery = strQuery.Replace("#loja#", dbConnection.CodLoja                                    );
                    
                    DataTable dados = dbConn.Select(strQuery);

                    if (dados.DefaultView.Count > 0)
                    {
                        strRefBasica = dados.DefaultView[0]["RefBasica"].ToString();
                        
                        if (dados.DefaultView.Count == 1)
                        {
                            decimal oPrecoVarejo     = (decimal)dados.DefaultView[0]["PrecoVarejo"    ];
                            decimal oPromocaoVarejo  = (decimal)dados.DefaultView[0]["PromocaoVarejo" ];
                            decimal oPrecoAtacado    = (decimal)dados.DefaultView[0]["PrecoAtacado"   ];
                            decimal oPromocaoAtacado = (decimal)dados.DefaultView[0]["PromocaoAtacado"];

                            lblPreco.Text        = String.Format("{0:C}", (oPromocaoVarejo  > 0 ? oPromocaoVarejo  : oPrecoVarejo ));
                            lblPrecoAtacado.Text = String.Format("{0:C}", (oPromocaoAtacado > 0 ? oPromocaoAtacado : oPrecoAtacado));
                            lblObs.Text          = String.Format("{0}"  , (oPromocaoVarejo  > 0 ? "Promoção Varejo":"Varejo Normal")+"/"
                                                                        + (oPromocaoAtacado > 0 ? "Promoção Atacado":"Atacado Normal"));
                            lblDesc.Text         = String.Format("{0}", dados.DefaultView[0]["Descricao"]);
                        }
                        else
                        {
                            DataGridView dgv = new DataGridView();
                            dgv.DataSource = dados;
                            dgv.Dock = DockStyle.Fill;
                            dgv.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
                            dgv.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                            dgv.ReadOnly = true;

                            Form frm = new Form();
                            frm.Text = "Consulta Preço [Ref: " + txtReferencia.Text.Trim() + "]";
                            frm.WindowState = FormWindowState.Maximized;
                            frm.Font = new Font("Courier New", 12);

                            frm.Controls.Add(dgv);
                            frm.ShowDialog(this);
                        }
                    }
                    else
                    {
                        lblPreco.Text = String.Format("{0:C}", 0);//"R$ 0,00";
                        lblObs.Text   = "Produto Não Encontrado";
                        lblDesc.Text  = "";
                    }

                    string strFullFileName = dbConnection.ImagesRootPath + "\\P" + strRefBasica + ".jpg";
                    if (System.IO.File.Exists(strFullFileName))
                    {
                        Image img = new System.Drawing.Bitmap(strFullFileName);
                        imgProd.Image = img;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    txtReferencia.SelectAll();
                }
            }
            #endregion
        }

        private void ConPreco_Load(object sender, EventArgs e)
        {
            lblTitulo.Text = dbConnection.Alias + " Consulta Preço";
            txtReferencia.Focus();

            lblAtacado.Visible = dbConnection.MostraAtacado;
            lblPrecoAtacado.Visible = dbConnection.MostraAtacado;
        }
    }
}